package com.cap.bts.crmdp.dealer.masterdata.dao.impl;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Types;
import java.util.List;

import javax.inject.Inject;

import org.hibernate.Session;
import org.hibernate.engine.SessionFactoryImplementor;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Repository;

import com.cap.bts.crmdp.dealer.masterdata.dao.IMarsDataConversionMemberDao;
import com.cap.bts.crmdp.dealer.masterdata.domain.MarsDataConversionMember;
import com.cap.bts.crmdp.dealer.masterdata.dto.DataConvertStoredProcedure;
import com.cap.bts.crmdp.dealer.masterdata.dto.MarsDataConversionMemberDto;
import com.cap.bts.crmdp.dealer.masterdata.dto.MarsDataConversionPortfolioDto;
import com.cap.bts.framework.common.dao.callback.NativeQueryBySqlJpaCallback;
import com.cap.bts.framework.common.dao.impl.BaseDaoImpl;
import com.cap.bts.framework.common.exception.AppException;

@Repository("marsDataConversionMemberDao")
public class MarsDataConversionMemberDaoImpl extends BaseDaoImpl<MarsDataConversionMember, Long> implements IMarsDataConversionMemberDao {
	@Inject
	public MarsDataConversionMemberDaoImpl(@Value("com.cap.bts.crmdp.dealer.masterdata.domain.MarsDataConversionMember")Class<MarsDataConversionMember> clazz) {
		super(clazz);
	}

	@Override
	public List<String> checkStoreCodeByStoreCode(String dearNumber, boolean memberFlag, boolean portfolioFlag ) {
		String sql = "";
		if(memberFlag){
			sql += ( "SELECT store_code FROM ti_mars_data_conversion_member"
					+ " where store_code = " + dearNumber );
		}
		
		if(memberFlag && portfolioFlag){
			sql += " union ";
		}
		
		if(portfolioFlag){
			sql += ( " SELECT store_code FROM ti_mars_data_conversion_portfolio"
					+ " where store_code = " + dearNumber );
		}
				
				
		return this.getJpaTemplate().executeFind(new NativeQueryBySqlJpaCallback<String>(sql));
	}
	
	
	
	@Override
	public DataConvertStoredProcedure callPrcDataConversion(Integer dealerNum, String dataType) throws SQLException {
		Session session = (org.hibernate.Session) getEntityManager().getDelegate();
		SessionFactoryImplementor sf = (SessionFactoryImplementor) session.getSessionFactory();
		Connection connection = null;
		CallableStatement cs = null;
		DataConvertStoredProcedure resultDto = new DataConvertStoredProcedure();
		try{
			connection = sf.getConnectionProvider().getConnection();
			
			cs = connection.prepareCall(" { call prc_data_conversion(?,?,?,?)}");
			cs.setInt(1, dealerNum);
			cs.setString(2, dataType);
			cs.registerOutParameter(3, Types.VARCHAR);
			cs.registerOutParameter(4, Types.VARCHAR);
			
			cs.execute();
			
			resultDto.setReturnCode(cs.getString(3));
			resultDto.setErrorMessage(cs.getString(4));  
			
			cs.close();
		}finally{
			try {
				connection.close();
			} catch (SQLException e) {
				cs = null;
				connection = null;
			}
		}
		return resultDto;
	}
	
	@Override
	public void batchInsertMember(String batchId, List<MarsDataConversionMemberDto> dataList) throws AppException {
		Session session = (org.hibernate.Session) getEntityManager().getDelegate();
		SessionFactoryImplementor sf = (SessionFactoryImplementor) session.getSessionFactory();
		Connection connection = null;
		PreparedStatement pstmt = null;
		try{
			connection = sf.getConnectionProvider().getConnection();
			connection.setAutoCommit(false);
			
			pstmt = connection.prepareStatement("INSERT INTO ti_mars_data_conversion_member  (  " +
	                "  line_num ,  " +
	                "  pcode ,  " +
	                "  member_code ,  " +
	                "  member_name ,  " +
	                "  mobile ,  " +
	                "  car_plate ,  " +
	                "  store_code ,  " +
	                "  mars_customer_code ,  " +
	                "  mars_vehicle_code ,  " +
	                "  mars_customer_name ,  " +
	                "  mars_phone_number ,  " +
	                "  batch_id   " +
	                ")  " +
	                "VALUES  " +
	                " (  " +
	                " ?,  " +
	                " ?,  " +
	                " ?,  " +
	                " ?,  " +
	                " ?,  " +
	                " ?,  " +
	                " ?,  " +
	                " ?,  " +
	                " ?,  " +
	                " ?,  " +
	                " ?, " +
	                " ? " +
	                " ) ");
			
			for (MarsDataConversionMemberDto marsDataConversionMemberDto : dataList) {
				pstmt.setString(1, marsDataConversionMemberDto.getNum());
				pstmt.setString(2, marsDataConversionMemberDto.getPcode());
				pstmt.setString(3, marsDataConversionMemberDto.getMemberCode());
				pstmt.setString(4, marsDataConversionMemberDto.getMemberName());
				pstmt.setString(5, marsDataConversionMemberDto.getMobile());
				pstmt.setString(6, marsDataConversionMemberDto.getCarPlate());
				pstmt.setString(7, marsDataConversionMemberDto.getStoreCode());
				pstmt.setString(8, marsDataConversionMemberDto.getMarsCustomerCode());
				pstmt.setString(9, marsDataConversionMemberDto.getMarsVehicleCode());
				pstmt.setString(10, marsDataConversionMemberDto.getMarsCustomerName());
				pstmt.setString(11, marsDataConversionMemberDto.getMarsPhoneNumber());
				pstmt.setString(12, batchId);
				
				
				pstmt.addBatch();
			}
			
			
			pstmt.executeBatch();
			
		}catch(Exception e){
			throw new AppException("ERROR", e);
		}finally{
			try {
				connection.setAutoCommit(true);
				pstmt.close();
				connection.close();
			} catch (SQLException e) {
				pstmt = null;
				connection = null;
			}
		}
	}

	@Override
	public void batchInsertPortfolio(String batchId, List<MarsDataConversionPortfolioDto> dataList) throws AppException {
		Session session = (org.hibernate.Session) getEntityManager().getDelegate();
		SessionFactoryImplementor sf = (SessionFactoryImplementor) session.getSessionFactory();
		Connection connection = null;
		PreparedStatement pstmt = null;
		try{
			connection = sf.getConnectionProvider().getConnection();
			connection.setAutoCommit(false);
			pstmt = connection.prepareStatement("INSERT INTO ti_mars_data_conversion_portfolio  ( " +
	                "    pcode , " +
	                "    member_code , " +
	                "    member_name , " +
	                "    mobile , " +
	                "    mars_customer_code , " +
	                "    car_plate , " +
	                "    mars_car_no , " +
	                "    card_no , " +
	                "    dms_card_no , " +
	                "    card_type , " +
	                "    dms_card_type , " +
	                "    store_code , " +
	                "    title , " +
	                "    dms_title , " +
	                "    creation_date , " +
	                "    validate_date , " +
	                "    due_date , " +
	                "    dp_status , " +
	                "    dms_status , " +
	                "    coupon_type , " +
	                "    free_service_times , " +
	                "    times_of_use , " +
	                "    times_left , " +
	                "    batch_id, " +
	                "    line_num " +
	                ") " +
	                "VALUES " +
	                "   ( " +
	                "   ? ,  ? , ? ,  ? ,  ? , " + 
	                "  ? ,   ? ,  ? ,  ? ,  ? , " +
	                "  ? ,   ? ,  ? ,  ? ,  ? , " +
	                "  ? ,   ? ,  ? ,  ? ,  ? , " +
	                "   ? ,  ? ,  ? , ? ,?" +
	                "   ) ");
			
			for (MarsDataConversionPortfolioDto marsDataConversionPortfolioDto : dataList) {
				pstmt.setString(1, marsDataConversionPortfolioDto.getPcode());
				pstmt.setString(2, marsDataConversionPortfolioDto.getMemberCode());
				pstmt.setString(3, marsDataConversionPortfolioDto.getMemberName());
				pstmt.setString(4, marsDataConversionPortfolioDto.getMobile());
				pstmt.setString(5, marsDataConversionPortfolioDto.getMarsCustomerCode());
				pstmt.setString(6, marsDataConversionPortfolioDto.getCarPlate());
				pstmt.setString(7, marsDataConversionPortfolioDto.getMarsCarNo());
				pstmt.setString(8, marsDataConversionPortfolioDto.getCardNo());
				pstmt.setString(9, marsDataConversionPortfolioDto.getDmsCardNo());
				pstmt.setString(10,marsDataConversionPortfolioDto.getCardType());
				pstmt.setString(11,marsDataConversionPortfolioDto.getDmsCardType());
				pstmt.setString(12,marsDataConversionPortfolioDto.getStoreCode());
				pstmt.setString(13,marsDataConversionPortfolioDto.getTitle());
				pstmt.setString(14,marsDataConversionPortfolioDto.getDmsTitle());
				if(marsDataConversionPortfolioDto.getCreationDate() != null){
					pstmt.setDate(15,new Date(marsDataConversionPortfolioDto.getCreationDate().getTime()));
				}else{
					pstmt.setDate(15, null);
				}
				
				if(marsDataConversionPortfolioDto.getValidateDate() != null){
					pstmt.setDate(16,new Date(marsDataConversionPortfolioDto.getValidateDate().getTime()));
				}else{
					pstmt.setDate(16,null);
				}
				
				if(marsDataConversionPortfolioDto.getDueDate() != null){
					pstmt.setDate(17,new Date(marsDataConversionPortfolioDto.getDueDate().getTime()));
				}else{
					pstmt.setDate(17,null);
				}
				pstmt.setString(18,marsDataConversionPortfolioDto.getDpStatus());
				pstmt.setString(19,marsDataConversionPortfolioDto.getDmsStatus());
				pstmt.setString(20,marsDataConversionPortfolioDto.getCouponType());
				pstmt.setString(21,marsDataConversionPortfolioDto.getFreeServiceTimes());
				pstmt.setString(22,marsDataConversionPortfolioDto.getTimesOfUse());
				pstmt.setString(23,marsDataConversionPortfolioDto.getTimesLeft());
				pstmt.setString(24, batchId);
				pstmt.setString(25, marsDataConversionPortfolioDto.getNum());
				
				
				pstmt.addBatch();
			}
			
			
			pstmt.executeBatch();
			
		}catch(Exception e){
			throw new AppException("ERROR", e);
			
		}finally{
			try {
				connection.setAutoCommit(true);
				pstmt.close();
				connection.close();
			} catch (SQLException e) {
				pstmt = null;
				connection = null;
			}
		}
	}

}
